﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using TimeTableManagement.Entities;
using TimeTableManagement;
using System.Data.OleDb;
using System.Web.Script.Serialization;
using TimeTableManagement.Models;

namespace TimeTableManagement.Controllers
{ 
    
    public class LecturerController : Controller
    {
        private QLSVContext db = new QLSVContext();

        //
        // GET: /Lecturer/

        public ViewResult Index()
        {
            var tb_people = db.Tb_People.Include(t => t.Tb_Config).Include(t => t.Tb_Department).Where(g=>g.type==14);
            return View(tb_people.ToList());
        }

        public ActionResult Timetable()
        {
            List<Tb_Timetable> listreturn = new List<Tb_Timetable>();
            
            int idlecturer = 0;
            try
            {
                idlecturer = (int)Session["IDPEOPLE"];
            }
            catch
            {
                return RedirectToAction("LogOn", "Account", new { ReturnUrl = "/Lecturer/Timetable" });
            }
            //kiem tra xem day co phai la gv hay khong
            int? type = db.Tb_People.Find(idlecturer).type;
            if (type != 14)
            {
                ViewBag.error = 1;
                return View(new List<Tb_Timetable>());
            }
            //lay hk hien tai
            var hkactive = db.Tb_Config.Where(g => g.keyword.Equals("termactive"));
            int HK = 0;
            foreach (Tb_Config _c in hkactive)
            {
                HK = Int32.Parse(_c.value);
                if (HK != 0) break;
            }
            if (HK == 0)
            {

                ModelState.AddModelError("", "Error System Timetable");
                return View(new List<Tb_Timetable>());
            }
            //Lay danh sach thoi khoa bieu
            var list = db.Tb_Timetable
                .Where(g => g.id_lecturer == idlecturer)
                .Where(g => g.id_term == HK);
            foreach (Tb_Timetable t in list)
            {
                //TimeTable time = new TimeTable();
                //time.id_course = t.id_course;
                //time.id_lecturer = t.id_lecturer;
                //time.id_room = t.id_room;
                //time.id_slot = t.id_slot;
                //time.yesorno = true;
                //time.code_course = t.Tb_Course.code;
                //time.name_course = t.Tb_Course.name;
                //time.name_lecturer = t.Tb_People.name;
                //time.name_room = t.Tb_Room.name;
                //time.name_slot = t.Tb_Slot.slot;
                listreturn.Add(t);
            }
            return View(listreturn);
        }
        //
        // GET: /Lecturer/Details/5

        public ViewResult Details(int id)
        {
            Tb_People tb_people = db.Tb_People.Find(id);
            return View(tb_people);
        }
    
        //
        // GET: /Lecturer/Create

        public ActionResult Create()
        {
            ViewBag.type = new SelectList(db.Tb_Config, "id", "keyword");
            ViewBag.id_department = new SelectList(db.Tb_Department, "id", "name");
            return View();
        } 

        //
        // POST: /Lecturer/Create

        [HttpPost]
        public ActionResult Create(Tb_People tb_people, String profile)
        {
            if (ModelState.IsValid)
            {
                if (checknotexits(tb_people))
                {
                    db.Tb_People.Add(tb_people);
                    db.SaveChanges();
                     
                    Tb_People peoplenew = new Tb_People();
                    var _listtem = db.Tb_People.Where(g => g.mssv.Equals(tb_people.mssv)).ToList();
                    foreach (Tb_People p in _listtem)
                    {
                        peoplenew = p;
                        break;
                    }
                    JavaScriptSerializer jss = new JavaScriptSerializer();
                    List<Profile> listprofile = jss.Deserialize<List<Profile>>(profile);
                    String email="";
                    foreach (Profile _profile in listprofile)
                    {
                        Tb_Profile_Value pro_value = new Tb_Profile_Value();
                        pro_value.id_person = peoplenew.id;
                        pro_value.code = peoplenew.mssv;
                        pro_value.id_keyworkprofile = _profile.id;
                        pro_value.value = _profile.value;
                        db.Tb_Profile_Value.Add(pro_value);
                        db.SaveChanges();
                        if (_profile.key.Equals("email")) email = _profile.value;
                    }
                    //Them mot user trong Tb_Userlogon voi username, password = mssv
                    Tb_Userlogin userlogin = new Tb_Userlogin();
                    userlogin.username = tb_people.mssv;
                    userlogin.password = Functions.Encrypt(tb_people.mssv);
                    userlogin.email = email;
                    userlogin.displayname = tb_people.name;
                    userlogin.id_role = 8;
                    userlogin.rememberme = false;
                    db.Tb_Userlogin.Add(userlogin);
                    db.SaveChanges();
                    return RedirectToAction("Index");
                }
                else
                {
                    ModelState.AddModelError("", "Bi trung MSSV.");
                }
            }

            ViewBag.id_department = new SelectList(db.Tb_Department, "id", "code", tb_people.id_department);
            return View(tb_people);
        }
        private bool checknotexits(Tb_People people)
        {
            var p = db.Tb_People.Where(g => g.mssv.Equals(people.mssv)).ToList();
            if (p.Count == 0) return true; //khong ton tai
            return false;//da ton tai
        }
        //
        // GET: /Lecturer/Edit/5
 
        public ActionResult Edit(int id)
        {
            Tb_People tb_people = db.Tb_People.Find(id);
            ViewBag.type = new SelectList(db.Tb_Config, "id", "keyword", tb_people.type);
            ViewBag.id_department = new SelectList(db.Tb_Department, "id", "name", tb_people.id_department);
            return View(tb_people);
        }

        //
        // POST: /Lecturer/Edit/5

        [HttpPost]
        public ActionResult Edit(Tb_People tb_people, String profile)
        {
            if (ModelState.IsValid)
            {
                JavaScriptSerializer jss = new JavaScriptSerializer();
                List<Profile> listprofile = jss.Deserialize<List<Profile>>(profile);
                foreach (Profile _profile in listprofile)
                {
                    var PR = db.Tb_Profile_Value.Find(_profile.id);
                    PR.value = _profile.value;
                    db.SaveChanges();
                }
                db.Entry(tb_people).State = EntityState.Modified;
                db.SaveChanges();
                return RedirectToAction("Index");
            }
            ViewBag.id_department = new SelectList(db.Tb_Department, "id", "code", tb_people.id_department);
            return View(tb_people);
        }

        //
        // GET: /Lecturer/Delete/5
 
        public ActionResult Delete(int id)
        {
            Tb_People tb_people = db.Tb_People.Find(id);
            return View(tb_people);
        }

        //
        // POST: /Lecturer/Delete/5

        [HttpPost, ActionName("Delete")]
        public ActionResult DeleteConfirmed(int id)
        {            
            Tb_People tb_people = db.Tb_People.Find(id);
            db.Tb_People.Remove(tb_people);
            db.SaveChanges();
            return RedirectToAction("Index");
        }

        public ActionResult Import(string filename)
        {
            if (filename == null)
            {
                ViewData["kq"] = "no";
                return View();
            }
            else
            {

                if (this.importpeople(filename)) ViewData["kq"] = "ok";
                else ViewData["kq"] = "un";
                return RedirectToAction("Index");
            }
        }

        public ActionResult Registry()
        {
            int id_person = 0;
            try
            {
                id_person = (int)Session["IDPEOPLE"];
            }
            catch {
                return RedirectToAction("LogOn", "Account", new { ReturnUrl = "/Lecturer/Registry" });
            }
            var _people = db.Tb_Profile_Value.Where(g => g.id_person == id_person).Where(h => h.Tb_Config.keyword.Equals("asscou"));
            if (_people.ToList().Count == 0)
            {
                ModelState.AddModelError("", "Ban chua dang ky mon hoc, vui long dang ky");
                return View(new List<AssignCourse>());
            }
            String ass = "";
            foreach (Tb_Profile_Value pv in _people)
            {
                ass = pv.value;
                break;
            }
            JavaScriptSerializer jss = new JavaScriptSerializer();
            List<AssignCourse> listprofile = jss.Deserialize<List<AssignCourse>>(ass);
            return View(listprofile);
        }

        //
        // POST: /AssiCourse/Create

        [HttpPost]
        public ActionResult Registry(String regcourse)
        {
            int id_person = 0;
            try
            {
                id_person = (int)Session["IDPEOPLE"];
            }
            catch
            {
                return RedirectToAction("LogOn", "Account", new { ReturnUrl = "/Lecturer/Registry" });
            }
            var _people = db.Tb_Profile_Value.Where(g => g.id_person == id_person).Where(h => h.Tb_Config.option.Equals("course"));
            Nullable<int> id_profile = null;
            if (_people.ToList().Count == 0)
            {
                //Chua dang ky mon
                //Tao moi Tb_Profile_Value
                Tb_People person = db.Tb_People.Find(id_person);
                Tb_Profile_Value profile = new Tb_Profile_Value();
                profile.id_person = id_person;
                profile.code = person.mssv;
                profile.value = regcourse;
                profile.id_keyworkprofile = 25;
                db.Tb_Profile_Value.Add(profile);
                db.SaveChanges();
            }
            else
            { //da dang ky mon
                //them nhung mon chua dang ky
                foreach (Tb_Profile_Value pv in _people)
                {
                    id_profile = pv.id;
                    break;
                }
                var ppr = db.Tb_Profile_Value.Find(id_profile);
                ppr.value = regcourse;
                db.SaveChanges();
            }
            return RedirectToAction("Registry", "Lecturer");
        }
        [HttpPost]
        public ActionResult EditPrior(String editprior)
        {
            int id_person = 0;
            try
            {
                id_person = (int)Session["IDPEOPLE"];
            }
            catch
            {
                return RedirectToAction("LogOn", "Account", new { ReturnUrl = "/Lecturer/Registry" });
            }
            Nullable<int> id_profile = null;
            var _people = db.Tb_Profile_Value.Where(g => g.id_person == id_person).Where(g=>g.id_keyworkprofile==25);
            foreach (Tb_Profile_Value pv in _people)
            {
                id_profile = pv.id;
                break;
            }
            var ppr = db.Tb_Profile_Value.Find(id_profile);
            ppr.value = editprior;
            db.SaveChanges();
            return RedirectToAction("Registry", "Lecturer");
        }

        public ActionResult RegSlot()
        {
            List<RegSlot> listprofile = new List<RegSlot>();
            try
            {
                int id_person = 0;
                try
                {
                    id_person = (int)Session["IDPEOPLE"];
                }
                catch
                {
                    return RedirectToAction("LogOn", "Account", new { ReturnUrl = "/Lecturer/RegSlot" });
                }
                var regs = db.Tb_Profile_Value
                    .Where(g => g.id_person == id_person)
                    .Where(h => h.id_keyworkprofile == 41);
                String js = regs.First<Tb_Profile_Value>().value;
                JavaScriptSerializer jss = new JavaScriptSerializer();
                listprofile = jss.Deserialize<List<RegSlot>>(js);
                if (listprofile.Count == 0) return View(new List<RegSlot>());
            }
            catch { }
            return View(listprofile);
        }

        [HttpPost]
        public ActionResult RegSlot(String regslot)
        {
            int id_person = 0;
            try
            {
                id_person = (int)Session["IDPEOPLE"];
            }
            catch
            {
                return RedirectToAction("LogOn", "Account", new { ReturnUrl = "/Lecturer/Registry" });
            }
            Tb_Profile_Value profile_value = new Tb_Profile_Value();

            profile_value.code = db.Tb_People.Find(id_person).mssv;
            profile_value.id_keyworkprofile = 41;
            profile_value.id_person = id_person;
            profile_value.value = regslot;
            db.Tb_Profile_Value.Add(profile_value);
            db.SaveChanges();
            var model = db.Tb_Slot.ToList();
            ViewBag.REGSLOT = regslot;
            JavaScriptSerializer jss = new JavaScriptSerializer();
            List<RegSlot> listprofile = jss.Deserialize<List<RegSlot>>(regslot);
            return View(listprofile);
        }

        private bool importpeople(String fi)
        {
            //string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~/Uploads/" + fi) + ";Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\";";
            string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/Uploads/" + fi) + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
            // Create the connection object
            OleDbConnection oledbConn = new OleDbConnection(connString);
            try
            {
                // Open connection
                oledbConn.Open();

                // Create OleDbCommand object and select data from worksheet Sheet1
                OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn);

                // Create new OleDbDataAdapter
                OleDbDataAdapter oleda = new OleDbDataAdapter();

                oleda.SelectCommand = cmd;

                // Create a DataSet which will hold the data extracted from the worksheet.
                DataSet ds = new DataSet();

                // Fill the DataSet from the data extracted from the worksheet.
                oleda.Fill(ds);
                int i = 0;
                DataRow r = null;
                while ((r = ds.Tables[0].Rows[i]) != null)
                {
                    Array t = r.ItemArray;
                    Tb_People people = new Tb_People();
                    people.mssv = (string)t.GetValue(0);
                    people.name = (string)t.GetValue(1);
                    people.type = 14;
                    string codedepar = (string)t.GetValue(2);
                    var _d = db.Tb_Department.Where(g => g.code.Equals(codedepar));
                    people.id_department = _d.First<Tb_Department>().id;
                    //Them Tb_People
                    if (checknotexits(people))
                    {
                        db.Tb_People.Add(people);
                        String email = (string)t.GetValue(3);
                        Tb_Userlogin userlogin = new Tb_Userlogin();
                        userlogin.username = people.mssv;
                        userlogin.password = Functions.Encrypt(people.mssv);
                        userlogin.displayname = people.name;
                        userlogin.email = email;
                        userlogin.id_role = 8;
                        userlogin.rememberme = false;
                        //Them Tb_Userlogin
                        db.Tb_Userlogin.Add(userlogin);
                        db.SaveChanges();
                        
                        //Them profile ""
                        var itemperson = db.Tb_People.Where(g => g.mssv.Equals(people.mssv));
                        int id_person = itemperson.First<Tb_People>().id;
                        var itemprofile = db.Tb_Config
                            .Where(g => g.option.Equals("profile"));
                        foreach (Tb_Config con in itemprofile)
                        {
                            Tb_Profile_Value profile_value = new Tb_Profile_Value();
                            profile_value.code = people.mssv;
                            profile_value.value = "";
                            profile_value.id_keyworkprofile = con.id;
                            profile_value.id_person = id_person;
                            QLSVContext _c = new QLSVContext();
                            _c.Tb_Profile_Value.Add(profile_value);
                            _c.SaveChanges();
                            _c.Dispose();
                        }
                    }i++;
                }
            }
            catch (Exception ex)
            {
                Response.Write(ex);
                return false;
            }
            finally
            {
                oledbConn.Close();
            }
            return true;
        }

        

        protected override void Dispose(bool disposing)
        {
            db.Dispose();
            base.Dispose(disposing);
        }
    }
    public class AssignCourse
    {
        int _id; //id_course
        String _code; //Ma mon
        int _prior; //do uu tien
        public int id
        {
            get { return _id; }
            set { _id = value; }
        }
        public int prior
        {
            get { return _prior; }
            set { _prior = value; }
        }
        public String code
        {
            get { return _code; }
            set { _code = value; }
        }
    }
    public class RegSlot
    {
        int _id; //id_slot
        String _slot; //Slot name
        int _prior; //do uu tien
        public int id
        {
            get { return _id; }
            set { _id = value; }
        }
        public int prior
        {
            get { return _prior; }
            set { _prior = value; }
        }
        public String slot
        {
            get { return _slot; }
            set { _slot = value; }
        }
    }

}